﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SpreadsheetGear;
using SpreadsheetGear.Data;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.DataAccessLayer.ESGRatings;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.Entities.ESGRatings;
using Sustainalytics.Utils;

namespace Sustainalytics.ESGRatings.BusinessLogic
{
    using Sustainalytics.DataLayer.EsgRatings;

    public class ReferenceUniversesManagement : IReferenceUniversesManagement
    {
        private readonly IEsgInputFileRepository _esgInputFileRepository;
        private readonly IReferenceUniversesRepository _referenceUniversesRepository;
        private readonly IClientDwRepository _clientDwRepository;
        private const string ReferenceUniverseWorksheet = "ReferenceUniverse";
        private const int CQIDIndex = 0;
        private const int CompanyNameIndex = 1;

        private ReferenceUniversesManagement(IClientDwRepository clientDwRepository,
            IEsgInputFileRepository esgInputFileRepository, IReferenceUniversesRepository referenceUniversesRepository)
        {
            _clientDwRepository = clientDwRepository;
            _esgInputFileRepository = esgInputFileRepository;
            _referenceUniversesRepository = referenceUniversesRepository;
        }

        public static IReferenceUniversesManagement GetInstance(IEsgInputFileRepository esgInputFileRepository,
            IClientDwRepository clientDwRepository, IReferenceUniversesRepository referenceUniversesRepository)
        {
            if (clientDwRepository == null)
            {
                throw new ArgumentException(
                    "No instance of IClientDwRepository provided for creating an IReferenceUniversesManagement!");
            }

            if (esgInputFileRepository == null)
            {
                throw new ArgumentException(
                    "No instance of IEsgInputFileRepository provided for creating an IReferenceUniversesManagement!");
            }

            if (referenceUniversesRepository == null)
            {
                throw new ArgumentException(
                    "No instance of IReferenceUniversesRepository provided for creating an IReferenceUniversesManagement!");
            }

            return new ReferenceUniversesManagement(clientDwRepository, esgInputFileRepository,
                referenceUniversesRepository);

        }


        public IList<string> ProcessLoadedReferenceUniverse()
        {
            IList<EsgInputFileComplete> latestUnprocessedFiles;
            return ProcessLoadedReferenceUniverse(out latestUnprocessedFiles);
        }

        public IList<string> ProcessLoadedReferenceUniverse(out IList<EsgInputFileComplete> unprocessedFiles)
        {
            unprocessedFiles = new List<EsgInputFileComplete>();
            var errorsLog = new List<string>();
            var latestUnprocessedFiles =
                _esgInputFileRepository.ListLatestUnprocessedFiles(EsgInputFileType.ReferenceUniverse);
            if (latestUnprocessedFiles.IsNullOrEmpty())
            {
                return errorsLog;
            }
            foreach (var referenceUniverseFile in latestUnprocessedFiles)
            {
                var referenceUniverseFileContentBytes = referenceUniverseFile.Content == null
                    ? -1
                    : referenceUniverseFile.Content.Length;

                if (referenceUniverseFileContentBytes <= 0)
                {
                    throw new ArgumentException(
                        "Invalid file uploaded for Reference Universe processing (i.e. null or empty byte array)!");
                }

                var referenceUniverseWorkbook =
                    Factory.GetWorkbookSet().Workbooks.OpenFromMemory(referenceUniverseFile.Content);

                referenceUniverseWorkbook.FullName = referenceUniverseFile.FileName;
                List<CorporateData> companies = new List<CorporateData>();

                string exception = ValidateReferenceUniverseCompanies(referenceUniverseWorkbook,
                    referenceUniverseFile.Name, ref companies);

                if (!string.IsNullOrEmpty(exception))
                {
                    errorsLog.Add(exception);
                }

                var referenceUniverse =
                    _referenceUniversesRepository.GetReferenceUniverseByName(referenceUniverseFile.Name);

                var refUnivCompanies =
                    (from u in companies select new ReferenceUniverseCompany(u.Id, u.CapitalIqId, u.CompanyName))
                        .ToList();

                if (referenceUniverse != null)
                {
                    referenceUniverse.Companies = refUnivCompanies;
                }
                else
                {
                    referenceUniverse = new ReferenceUniverse()
                    {
                        Id = Guid.NewGuid(),
                        Name = referenceUniverseFile.Name,
                        Companies = refUnivCompanies
                    };
                }
                _referenceUniversesRepository.SaveReferenceUniverse(referenceUniverse);
                latestUnprocessedFiles.Add(referenceUniverseFile);
            }

            return errorsLog;
        }

        public IList<ReferenceUniverse> GetReferenceUniverses(bool includeCompanies)
        {
            return _referenceUniversesRepository.ListReferenceUniverses(includeCompanies);
        }

        public byte[] GetESGFileByName(string referenceUniverseName)
        {
            var file = _esgInputFileRepository.GetESGFilesByFilter(x=>x.Name.Equals(referenceUniverseName));
            return file.Content;
        }





        private string ValidateReferenceUniverseCompanies(IWorkbook referenceUniverseWorkbook, string referenceUniverseName, ref List<CorporateData> companies)
        {
            var errorsLog = "";
            List<string> capitalIQIds;
            GetCompanyRowsFromExcel(referenceUniverseWorkbook, out capitalIQIds);
            if (capitalIQIds.Any())
            {
                companies = _clientDwRepository.GetCorporateDataByCapitalIQID(capitalIQIds);
            }
            var dbCapitalIQIds = companies.Select(a => a.CapitalIqId.ToString().ToUpperInvariant()).OrderBy(c => c).ToList();
            var excelCapitalIQIds = capitalIQIds.Select(a =>
            {
                var data = new string(a.Where(Char.IsDigit).ToArray());
                if (!string.IsNullOrEmpty(data))
                {
                    if (a.ToLowerInvariant().StartsWith("iqt")) data = string.Format("-{0}", data);
                }
                return data;

            }).Where(a=>!string.IsNullOrEmpty(a)).OrderBy(c => c).ToList();

            var diferences = excelCapitalIQIds.Except(dbCapitalIQIds).ToList();

            if (diferences.Any())
            {
                errorsLog = "Validation errors for Reference Universe '" + referenceUniverseName +"': "+ Environment.NewLine +
                                            "Companies not found for the following Capital IQ IDs:" + string.Join(", ", diferences);
            }
            return errorsLog;
        }

        private List<Tuple<string, string>> GetCompanyRowsFromExcel(IWorkbook referenceUniverseWorkbook, out List<string> capitalIqIds)
        {
            var companies = new List<Tuple<string, string>>();
            var outCapitalIqIds = new List<string>();
            var companyRows = referenceUniverseWorkbook.Worksheets[ReferenceUniverseWorksheet];

            var excelDataTable =
                referenceUniverseWorkbook.GetDataSet(GetDataFlags.NoColumnHeaders).Tables[
                    companyRows.Name];

            //remove header
            excelDataTable.Rows.RemoveAt(0);


            var refUnivDataView = new DataView(excelDataTable)
            {
                RowFilter = "Column1 IS NOT NULL AND Column1 <> '' AND Column2 IS NOT NULL AND Column2 <> ''",
                Sort = "Column2 ASC"
            };
            var dataTable = refUnivDataView.ToTable();
            foreach (DataRow row in dataTable.Rows)
            {
                string cqid = row[CQIDIndex].ToString().Trim();
                string companyName = row[CompanyNameIndex].ToString().Trim();
                if (!outCapitalIqIds.Contains(cqid))
                {
                    outCapitalIqIds.Add(cqid);
                }
                companies.Add(new Tuple<string, string>(cqid, companyName));
            }
            capitalIqIds = outCapitalIqIds;

            return companies;
        }

        public string SaveReferenceUniverse(byte[] dataFile, string fileName, string universeName, string contentType)
        {
            var datafileContentBytes = dataFile == null ? -1 : dataFile.Length;
            if (datafileContentBytes <= 0)
            {
                throw new ArgumentException("No file uploaded for Reference Universe (i.e. null or empty byte array)!");
            }
            var errorsLog = new StringBuilder();
            string validationResult = ValidateReferenceUniverseFile(dataFile);
            
            if (!string.IsNullOrEmpty(validationResult))
            {
                errorsLog.AppendLine(validationResult);
            }
            var referenceUniverseWorkbook = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(dataFile);
            

            var esgInputFile = new EsgInputFileComplete
            {
                Name = universeName,
                Id = Guid.NewGuid(),
                Type = EsgInputFileType.ReferenceUniverse,
                FileName = fileName,
                ContentType = contentType,
                Content = dataFile
            };
            if (errorsLog.Length == 0)
            {
                _esgInputFileRepository.AddFile(esgInputFile);
            }
            var companies = new List<CorporateData>();
            string validationMessage = ValidateReferenceUniverseCompanies(referenceUniverseWorkbook, universeName, ref companies);
            if (!string.IsNullOrEmpty(validationMessage))
            {
                errorsLog.Append(validationMessage);
            }
            return errorsLog.ToString();
        }

        private string ValidateReferenceUniverseFile(byte[] dataFile)
        {
            var excelWorkbook = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(dataFile);
            //get and verify excel sheet
            var worksheet = excelWorkbook.Worksheets[ReferenceUniverseWorksheet];

            if (worksheet == null)
            {
                return "ReferenceUniverse worksheet is missing from the excel template!";
            }

            if (worksheet.Cells[0, 0].Text.Trim().ToUpperInvariant() != "CAPITAL IQ ID" || //first field in template 
                    worksheet.Cells[0, 1].Text.ToUpperInvariant() != "COMPANY NAME")// second field in the template
            {
                return
                    "The Reference Universe Template does not comply with the known format! It must have 2 columns with the headers CAPITAL IQ ID and COMPANY NAME in this exact order.";
            }
            if (worksheet.Cells[1, 0].Text.Trim().Length < 1 &&
                    worksheet.Cells[1, 1].Text.Length < 1)
            {
                return "The Reference Universe Template can not be empty";
            }

            return "";
        }
    }
}
